package connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Activity;

public class ActivityConnection {

	
    public void addActivityToDB(Activity oActivity, int projectId){		
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement statement = con.createStatement() ;
			statement.executeUpdate("INSERT INTO ACTIVITY(ID, DESCRIPTION, STARTDATE, ESTIMATEDHOURS, EXACTHOURS," +
					                 "DELIVERABLE,PROJECTID) VALUES("+oActivity.getNumber()+", '"
					                 +oActivity.getDesc()+"', '"+oActivity.getStartDate()+"', "
					                 +oActivity.getEstimatedHours()+", "+oActivity.getExactHours()+",'"
					                 +oActivity.getDeliverable()+"', "+projectId+")");
		    statement.close();		
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
    
	public void removeActivityFromDB(int id){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement statement = con.createStatement() ;			
			statement.executeQuery("DELETE FROM ACTIVITY WHERE ID = "+id);			
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
	
    public void updateActivityFromDB(int id,String desc,String startDate,String deliverable){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		try {
			
			Statement statement = con.createStatement() ;			
			statement.executeQuery("UPDATE ACTIVITY SET DESCRIPTION = '"+ desc +"',STARTDATE = '"+startDate+"',DELIVERABLE='"+deliverable+"' WHERE ID = "+id);
            statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
	}
	
	public ArrayList<Activity> getAllActivitiesFromDB(){
		
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Activity> oActivityList = new ArrayList<Activity>();
		
		try {
			
			Statement statement = con.createStatement() ;
			
			ResultSet rset = statement.executeQuery("select * from Activity");
			
			while (rset.next()) {
				Activity oActivity = new Activity();
				oActivity.setNumber(rset.getInt(1));
				oActivity.setDesc(rset.getString(2));
				oActivity.setStartDate(rset.getString(3));
				oActivity.setEstimatedHours(rset.getInt(4));
				oActivity.setExactHours(rset.getInt(5));
				oActivity.setDeliverable(rset.getString(6));
				//oActivity.setProject(rset.getInt(7));
				
				oActivityList.add(oActivity);
				}
			rset.close();
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oActivityList;
		
	}
	
	public ArrayList<Activity> getAllActivityofProject(int projectId){
		ConnectionControl mgr = ConnectionControl.getInstance();
		Connection con = mgr.getConnection();
		
		ArrayList<Activity> oActivityList = new ArrayList<Activity>();
		
		try {
			
			Statement stetement = con.createStatement() ;			
			ResultSet rset = stetement.executeQuery("select * from activity where projectid = "+ projectId);			
			while (rset.next()) {
				Activity oActivity = new Activity();
				
				oActivity.setNumber(rset.getInt(1));
				oActivity.setDesc(rset.getString(2));
				oActivity.setStartDate(rset.getString(3));
				oActivity.setEstimatedHours(rset.getInt(4));
				oActivity.setExactHours(rset.getInt(5));
				oActivity.setDeliverable(rset.getString(6));
				
				
				oActivityList.add(oActivity);
				}
			rset.close();
			stetement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		mgr.closeConnection(con);
		
		return oActivityList;
	}

}
